from sqlalchemy import Column, Integer, String, Text, DateTime, Date, Boolean, ForeignKey, create_engine
from sqlalchemy.types import DECIMAL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from typing import Any
import datetime
from common.log import logger
from config import base_config

Base = declarative_base()


class Customers(Base):
    """
    Customer model mapped to the 'customer' table in the database.
    """
    __tablename__ = 'customers'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="Customer Name")
    phone = Column(String(14), default="None")
    address = Column(String(200), nullable=False)
    presenter = Column(String(30), default="我")
    register_time = Column(DateTime, default=datetime.datetime.utcnow)
    note = Column(Text, default="None")

    # Relationship
    orders = relationship("Orders", back_populates="customer")

    def __repr__(self):
        return "Customers(id={}, name={}, phone={}, address={}, presenter={}, lastLoginTime={}, note={})".format(
            self.id, self.name, self.phone, self.address, self.presenter, self.register_time, self.note
        )

    def __eq__(self, other: Any) -> bool:
        if not isinstance(other, self.__class__):
            return False
        return (self.name == other.name and self.address == other.address and
                self.phone == other.phone and self.presenter == other.presenter) or \
            (self.id is not None and other.id is not None and self.id == other.id)


class Users(Base):
    """
    User model mapped to the 'users' table in the database.
    User is the person who uses the app to manage the orders, not the customer.
    """
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    address = Column(String(200), nullable=False)
    # wx_user_id = Column(String(100), unique=True)  这个不是唯一标识符，弃用
    nickname = Column(String(100), unique=True, nullable=False)
    phone = Column(String(27), default='None')
    password_hashed = Column(String(255), nullable=True)
    register_time = Column(DateTime, default=datetime.datetime.utcnow)
    role = Column(String(30), default='USER')
    note = Column(Text, default='NULL', nullable=True)
    is_deleted = Column(Boolean, default=False)

    # Relationships
    kumquats = relationship("Kumquats", back_populates="user")
    orders = relationship("Orders", back_populates="user")
    chats = relationship("Chat", back_populates="user")

    def __repr__(self):
        return "Users(id={}, name={}, address={}, phone={}, password_hashed={}, register_time={}, role={}, note={})".format(
            self.id, self.name, self.phone, self.address, self.password_hashed, self.register_time, self.role, self.note
        )


class Kumquats(Base):
    """
    Kumquat model mapped to the 'kumquat' table in the database.
    """
    __tablename__ = 'kumquats'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(5), nullable=False)
    packing = Column(String(5), default='0')  # Not used
    format = Column(String(5), nullable=False)
    original_price = Column(DECIMAL(precision=5, scale=2), default=0)
    price = Column(DECIMAL(precision=5, scale=2), default=0)
    users_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    # profit = Column(DECIMAL(precision=5, scale=2), default=0)
    pricing_date = Column(Date, default=datetime.datetime.now(datetime.UTC).date())
    note = Column(Text, default='')

    # Relationships
    user = relationship("Users", back_populates="kumquats")
    orders = relationship("Orders", back_populates="kumquat")

    def __repr__(self):
        return "Kumquat(id={}, name={}, format={}, original_price={}, price={}, users={}, pricing_date={}, " \
               "note={})".format(
                self.id, self.name, self.format, self.original_price, self.price, self.user,
                self.pricing_date, self.note
                )

    @staticmethod
    def to_str(kumquat: Any):
        if isinstance(kumquat, Kumquats):
            return "🍊名称：【{} {}】 \n🍊售价：【{}】".format(kumquat.name, kumquat.format, kumquat.price)
        else:
            return "{}".format(kumquat)


class Orders(Base):
    """
    Order model mapped to the 'order' table in the database.
    """
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    customers_id = Column(Integer, ForeignKey('customers.id'), nullable=False)
    kumquats_id = Column(Integer, ForeignKey('kumquats.id'), nullable=False)
    users_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    quantity = Column(Integer, nullable=False)
    on_sold = Column(DECIMAL(precision=3, scale=2), default=1)
    amount = Column(DECIMAL(precision=10, scale=2), default=0)
    book_date = Column(DateTime, default=datetime.datetime.now)
    status = Column(String(10), default='已下单')
    order_delivery_code = Column(String(20), default='None')
    note = Column(Text, nullable=True, default='CURRENT')

    # Relationships
    customer = relationship("Customers", back_populates="orders")
    kumquat = relationship("Kumquats", back_populates="orders")
    user = relationship("Users", back_populates="orders")

    def __repr__(self):
        return "Order(id={}, customer={}, kumquat={}, quantity={}, amount={}, book_date={}, status={}, note={})".format(
            self.id, self.customer, self.kumquat, self.quantity, self.amount, self.book_date, self.status,
            self.note
        )

    def __str__(self):
        print("Order(id={}, customer={}, kumquat={}, quantity={}, amount={}, book_date={}, status={}, note={})".format(
            self.id, self.customer, self.kumquat, self.quantity, self.amount, self.book_date, self.status,
            self.note
        ))

    @staticmethod
    def to_str(order: Any):
        if isinstance(order, Orders):
            return "🍊客户：{}-{}-{} \n🍊商品：{} {} \n🍊数量：{} \n🍊金额：{} \n🍊备注：{}".format(
                order.customer.name, order.customer.address, order.customer.phone,
                order.kumquat.name, order.kumquat.format,
                order.quantity, order.amount, order.note
            )
        else:
            return "{}".format(order)

    def get_amount(self):
        if not isinstance(self.kumquat, Kumquats):
            raise RuntimeError("kumquats is Not define, can not calculate the price!")
        amount = self.kumquat.price * self.quantity * self.on_sold
        self.amount = amount if '赠送' not in self.note else self.kumquat.original_price * self.quantity * -1


class Chat(Base):
    """
    Chat model mapped to the 'chat' table in the database.
    """
    __tablename__ = 'chats'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    query = Column(Text, nullable=False)
    answer = Column(Text, nullable=False)
    ai_answer = Column(Text, default="['']")
    query_time = Column(DateTime, default=datetime.datetime.now)
    answer_time = Column(DateTime, default=datetime.datetime.now)
    actions = Column(Text, nullable=False)
    raw_msg = Column(Text, nullable=False)
    note = Column(Text, nullable=True, default='')

    # Relationships
    user = relationship("Users", back_populates="chats")

    def __repr__(self):
        return "Chat(id={}, user={}, query={}, answer={}, ai_answer={}, query_time={}, answer_time={}, " \
               "actions={}, raw_msg={}, note={})".format(
            self.id, self.user, self.query, self.answer, self.ai_answer,
            self.query_time, self.answer_time, self.actions, self.raw_msg, self.note
        )

    def is_group(self):
        return self.raw_msg.get('chattype') == 'group'


# Database session setup
engine = create_engine(base_config.DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


def get_db():
    """Get database session"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    """
    Initialize the database.
    """
    # Create all tables
    Base.metadata.create_all(bind=engine)
    
    db = SessionLocal()
    try:
        # Check if admin users exist
        admin_exists = db.query(Users).filter(Users.id == 0).first() is not None
        anonym_exists = db.query(Users).filter(Users.id == 1).first() is not None
        test_manager_exists = db.query(Users).filter(Users.id == 2).first() is not None
        test_user_exists = db.query(Users).filter(Users.id == 3).first() is not None
        
        if not admin_exists:
            admin_user = Users(id=0, name='admin', phone='None', password_hashed='None',
                             nickname='kumquat king robot admin', role='ADMIN', address='None')
            db.add(admin_user)
            
        if not anonym_exists:
            anonym_user = Users(id=1, name='anonymous', phone='None', password_hashed='None',
                              nickname='陌生人', role='ANONYMOUS', address='None', note='Anonymous user')
            db.add(anonym_user)
            
        if not test_manager_exists:
            test_manager = Users(id=2, name='test_manager', phone='None', password_hashed='None',
                               nickname='玲玲', role='MANAGER', address='None', note='Test manager')
            db.add(test_manager)
            
        if not test_user_exists:
            test_user = Users(id=3, name='test_user', phone='None', password_hashed='None',
                            nickname='东东', role='USER', address='None', note='Test user')
            db.add(test_user)

        # Load default kumquats
        import pandas as pd
        default_kumquats = pd.read_csv('./data/templates/default_kumquat.csv')
        default_kumquats['note'] = 'CURRENT'
        default_kumquats['pricing_date'] = datetime.datetime.now().date()  # 直接使用 date 对象
        default_kumquats['users_id'] = 0
        default_kumquats['id'] = range(1, len(default_kumquats) + 1)
        
        for _, row in default_kumquats.iterrows():
            existing_kumquat = db.query(Kumquats).filter(Kumquats.id == row['id']).first()
            if not existing_kumquat:
                kumquat = Kumquats(**row.to_dict())
                db.add(kumquat)
        
        db.commit()
        logger.info('[Init DB] Database initialized successfully. Admin user, default kumquat created.')
        
    except Exception as e:
        db.rollback()
        logger.error(f'[Init DB] Error initializing database: {e}')
        raise
    finally:
        db.close()
